package music.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import music.entity.Manager;
import music.entity.Page;
import music.entity.Song;
import music.entity.Topics;
import music.util.DBUtil;
import musicdao.MusicDao;

//����
public class MusicDaoImpl implements MusicDao<Song> {
	private static Logger Log = LoggerFactory.getLogger(MusicDaoImpl.class);
	public final static MusicDaoImpl DAO =  new MusicDaoImpl();
	@Override
	// 1.�������а񣨰��������ɸѡ��
	public List<Song> listening() {
		List<Song> songList = new ArrayList<Song>();
		Connection conn = DBUtil.getConnection();
		String sql = "SELECT * FROM tb_song order by hits DESC limit 8";
		PreparedStatement ps = null;
		ResultSet rs = null;
		Song son=null;
		try {
			ps = conn.prepareStatement(sql);		
			rs = ps.executeQuery();// ��ѯ
			while (rs.next()) {
				son = new Song();
				son.setDownload(rs.getInt("download"));
				son.setFileSize(rs.getString("fileSize"));
				son.setFileURL(rs.getString("fileURL"));
				son.setFormat(rs.getString("format"));
				son.setHits(rs.getInt("hits"));
				son.setId(rs.getInt("id"));
				son.setSinger(rs.getString("singer"));
				son.setSongName(rs.getString("songName"));
				son.setSongType(rs.getString("songType"));
				son.setSpecialName(rs.getString("specialName"));
				son.setUpTime(rs.getDate("upTime"));
				songList.add(son);
			}
			
		} catch (Exception e) {
			Log.error("���������ִ���", e);
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return songList;
	}

	@Override
	// 2.�������а񣨰������أ���ɸѡ��
	public List<Song> download() {
		List<Song> songList = new ArrayList<Song>();
		Connection conn = DBUtil.getConnection();
		String sql = "SELECT * FROM tb_song order by download DESC limit 8";
		PreparedStatement ps = null;
		ResultSet rs = null;
		Song son=null;
		try {
			ps = conn.prepareStatement(sql);		
			rs = ps.executeQuery();// ��ѯ
			while (rs.next()) {
				son = new Song();
				son.setDownload(rs.getInt("download"));
				son.setFileSize(rs.getString("fileSize"));
				son.setFileURL(rs.getString("fileURL"));
				son.setFormat(rs.getString("format"));
				son.setHits(rs.getInt("hits"));
				son.setId(rs.getInt("id"));
				son.setSinger(rs.getString("singer"));
				son.setSongName(rs.getString("songName"));
				son.setSongType(rs.getString("songType"));
				son.setSpecialName(rs.getString("specialName"));
				son.setUpTime(rs.getDate("upTime"));
				songList.add(son);
			}
			
		} catch (Exception e) {
			Log.error("�������а���ִ���", e);
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return songList;
	}

	@Override
	// ��ݸ������ֱ���ʾ��Ӧ����
	public Page<Song> songTopic(int id,int pageNo) {
		Page<Song> pageList = new Page<Song>();
		Connection conn = DBUtil.getConnection();
		String sql1 = "SELECT count(*) FROM tb_song WHERE songType=?";
		String sql2 = "SELECT * FROM tb_song WHERE songType=? limit ?,? ";
		
		PreparedStatement ps = null;
		ResultSet rs = null;
		Song son=null;
		int sum = 0;
		try {
			ps = conn.prepareStatement(sql1);
			ps.setInt(1,id);// �������id			
			rs = ps.executeQuery();// ��ѯ
			if(rs.next()){
				sum = rs.getInt(1);
			}
			//�ȹر�����
			DBUtil.close(null, ps, rs);
			ps = conn.prepareStatement(sql2);
			ps.setInt(1,id);// �������id		
			ps.setInt(2, (pageNo-1)*10);
			ps.setInt(3, 10);
			rs = ps.executeQuery();// ��ѯ
			while (rs.next()) {
				son = new Song();
				son.setDownload(rs.getInt("download"));
				son.setFileSize(rs.getString("fileSize"));
				son.setFileURL(rs.getString("fileURL"));
				son.setFormat(rs.getString("format"));
				son.setHits(rs.getInt("hits"));
				son.setId(rs.getInt("id"));
				son.setSinger(rs.getString("singer"));
				son.setSongName(rs.getString("songName"));
				son.setSongType(rs.getString("songType"));
				son.setSpecialName(rs.getString("specialName"));
				son.setUpTime(rs.getDate("upTime"));
				pageList.getDatas().add(son);
			}
			pageList.setPageNums(sum%10+sum%10==0?0:1);
			
		} catch (Exception e) {
			Log.error("���������ִ���", e);
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}return pageList;
	}

	@Override
	// ��ݲ�ͬ��������������ר�������֣�����������
	public Page<Song> search(String condition,String choice,int pageno) {		
		Page<Song> pageList = new Page<Song>();
		Connection conn = DBUtil.getConnection();
		String sql1 = "";
		/*
		 * �˷������и�ģ��������Լ��ķ�ʽ
		 * 1�����������û�����룺
		 */
		if (condition.length()==0) {
			sql1 = "SELECT count(*) FROM tb_song order by '"+choice+"'";
		} else {
			/*
			 * ��������룺�����������ݺ�ѡ�����ж�ʹ���Ǹ�sql��䣡
			 */
				sql1 = "SELECT count(*) FROM tb_song where '"+choice+"' like '%"+condition+"%'" ;
		}
		String sql2 ="SELECT * FROM tb_song where '"+choice+"' like '%"+condition+"%'limit ?,?" ;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Song son=null;
		int sum = 0;
		try {
			ps = conn.prepareStatement(sql1);			
			rs = ps.executeQuery();// ��ѯ
			if(rs.next()){
				sum = rs.getInt(1);
				System.out.println(sum+">>>>>>>>>>>>>>>>");
			}
			//�ȹر�����
			DBUtil.close(null, ps, rs);
			ps = conn.prepareStatement(sql2);
			ps.setInt(1, (pageno-1)*10);
			ps.setInt(2, 10);
			rs = ps.executeQuery();// ��ѯ
			while (rs.next()) {
				son = new Song();
				son.setDownload(rs.getInt("download"));
				son.setFileSize(rs.getString("fileSize"));
				son.setFileURL(rs.getString("fileURL"));
				son.setFormat(rs.getString("format"));
				son.setHits(rs.getInt("hits"));
				son.setId(rs.getInt("id"));
				son.setSinger(rs.getString("singer"));
				son.setSongName(rs.getString("songName"));
				son.setSongType(rs.getString("songType"));
				son.setSpecialName(rs.getString("specialName"));
				son.setUpTime(rs.getDate("upTime"));
				pageList.getDatas().add(son);
			}	
			pageList.setPageNums((sum/10)+(sum%10==0?0:1));
		} catch (Exception e) {
			Log.error("�����������ִ���", e);
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}return pageList;
	}
	//����Ա������
	// ��ݲ�ͬ��������������ר�������֣�����������
		public Page<Song> adminsearch(int id ,String condition,String choice,int pageno) {
			if(id==0){
				return search(condition, choice, pageno);
			}
			
			Page<Song> pageList = new Page<Song>();
			Connection conn = DBUtil.getConnection();
			String sql1 = "";
			/*
			 * �˷������и�ģ��������Լ��ķ�ʽ
			 * 1�����������û�����룺
			 */
			if (condition.length()==0) {
				sql1 = "SELECT count(*) FROM tb_song where songType="+id+" order by "+choice;
			} else {
				/*
				 * ��������룺�����������ݺ�ѡ�����ж�ʹ���Ǹ�sql��䣡
				 */
					sql1 = "SELECT count(*) FROM tb_song where "+choice+" like '%"+condition+"%' and songType="+id ;
			}
			String sql2 ="SELECT * FROM tb_song where "+choice+" like '%"+condition+"%' and songType = "+id+" limit ?,?" ;
			PreparedStatement ps = null;
			ResultSet rs = null;
			Song son=null;
			int sum = 0;
			try {
				ps = conn.prepareStatement(sql1);			
				rs = ps.executeQuery();// ��ѯ
				if(rs.next()){
					sum = rs.getInt(1);
					System.out.println(sum+">>>>>>>>>>>>>>>>");
				}
				//�ȹر�����
				DBUtil.close(null, ps, rs);
				ps = conn.prepareStatement(sql2);
				ps.setInt(1, (pageno-1)*10);
				ps.setInt(2, 10);
				rs = ps.executeQuery();// ��ѯ
				while (rs.next()) {
					son = new Song();
					son.setDownload(rs.getInt("download"));
					son.setFileSize(rs.getString("fileSize"));
					son.setFileURL(rs.getString("fileURL"));
					son.setFormat(rs.getString("format"));
					son.setHits(rs.getInt("hits"));
					son.setId(rs.getInt("id"));
					son.setSinger(rs.getString("singer"));
					son.setSongName(rs.getString("songName"));
					son.setSongType(rs.getString("songType"));
					son.setSpecialName(rs.getString("specialName"));
					son.setUpTime(rs.getDate("upTime"));
					pageList.getDatas().add(son);
				}	
				pageList.setPageNums((sum/10)+(sum%10==0?0:1));
			} catch (Exception e) {
				Log.error("�����������ִ���", e);
				e.printStackTrace();
			} finally {
				DBUtil.close(conn, ps, rs);
			}return pageList;
		}

	@Override
	//5/ ��ݸ�������������
	public Page<Song> searchTop(String typename,int pageno) {
		
		Page<Song> pageList = new Page<Song>();
		Connection conn = DBUtil.getConnection();
		String sql1 = "SELECT * FROM tb_song WHERE songType=?";
		String sql2 = "SELECT * FROM tb_song WHERE songType=? limit ?,?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		Song son=null;
		int sum = 0;
		try {
			ps = conn.prepareStatement(sql1);
			ps.setString(1,typename);// ����������			
			rs = ps.executeQuery();// ��ѯ
			if(rs.next()){
				sum = rs.getInt(1);
			}
			//�ȹر�����
			DBUtil.close(null, ps, rs);
			ps = conn.prepareStatement(sql2);
			ps.setString(1,typename);// �������id		
			ps.setInt(2, (pageno-1)*10);
			ps.setInt(3, 10);
			rs = ps.executeQuery();// ��ѯ
			while (rs.next()) {
				son = new Song();
				son.setDownload(rs.getInt("download"));
				son.setFileSize(rs.getString("fileSize"));
				son.setFileURL(rs.getString("fileURL"));
				son.setFormat(rs.getString("format"));
				son.setHits(rs.getInt("hits"));
				son.setId(rs.getInt("id"));
				son.setSinger(rs.getString("singer"));
				son.setSongName(rs.getString("songName"));
				son.setSongType(rs.getString("songType"));
				son.setSpecialName(rs.getString("specialName"));
				son.setUpTime(rs.getDate("upTime"));
				pageList.getDatas().add(son);
			}
			pageList.setPageNums(sum%10+sum%10==0?0:1);
			
		} catch (Exception e) {
			Log.error("���������ִ���", e);
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}return pageList;
	}

	@Override
	// 6/��ʾר�����������ϴ��ר����4����
	public List<String> searchTop() {
		List<String> specialList = new ArrayList<String>();
		Connection conn = DBUtil.getConnection();
		String sql = "SELECT specialName AS ר����, SUM(hits) AS �ȶ� FROM tb_song  WHERE specialName IN (SELECT specialName FROM tb_song GROUP BY specialName)GROUP BY specialName ORDER BY �ȶ� DESC LIMIT 4";
		PreparedStatement ps = null;
		ResultSet rs = null;
		String topics=null;
		try {
			ps = conn.prepareStatement(sql);			
			rs = ps.executeQuery();// ��ѯ
			while (rs.next()) {
				topics = rs.getString("ר����");				
				specialList.add(topics);
			}
			
		} catch (Exception e) {
			Log.error("�������������ִ���", e);
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}return specialList;
		
	}

	@Override
	//7�� �¸��ٵݣ�10������
	public List<Song> searchNew() {
		List<Song> songList = new ArrayList<Song>();
		Connection conn = DBUtil.getConnection();
		String sql = "SELECT * FROM tb_song ORDER BY upTime DESC LIMIT 10";
		PreparedStatement ps = null;
		ResultSet rs = null;
		Song son=null;
		try {
			ps = conn.prepareStatement(sql);		
			rs = ps.executeQuery();// ��ѯ
			while (rs.next()) {
				son = new Song();
				son.setDownload(rs.getInt("download"));
				son.setFileSize(rs.getString("fileSize"));
				son.setFileURL(rs.getString("fileURL"));
				son.setFormat(rs.getString("format"));
				son.setHits(rs.getInt("hits"));
				son.setId(rs.getInt("id"));
				son.setSinger(rs.getString("singer"));
				son.setSongName(rs.getString("songName"));
				son.setSongType(rs.getString("songType"));
				son.setSpecialName(rs.getString("specialName"));
				son.setUpTime(rs.getDate("upTime"));
				songList.add(son);
			}
			
		} catch (Exception e) {
			Log.error("�¸��ٵݳ��ִ���", e);
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}return songList;
	}

	@Override
	//8�� ��ʾ���֣����ո���ĸ�������ʾ����
	public List<String> geshou() {
		List<String> singerList=new ArrayList<String>();
		Connection conn = DBUtil.getConnection();
		String sql = "SELECT singer,COUNT(*) as ��������  FROM tb_song WHERE singer IN (SELECT singer FROM tb_song GROUP BY singer )GROUP BY singer ORDER BY �������� DESC limit 5";
		PreparedStatement ps = null;
		ResultSet rs = null;
		String singer=null;
		try {
			ps = conn.prepareStatement(sql);		
			rs = ps.executeQuery();// ��ѯ
			while(rs.next()){
				singer=rs.getString("singer");//��ȡ����
			    singerList.add(singer);//��������ӵ������з��������ʾ
			}			
			
		} catch (Exception e) {
			Log.error("��ʾ���ֳ��ִ���", e);
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}return singerList;
	}
	
	

	@Override
	//9�� ���������·����ȵ�������������ʾ��������ǰ��ĸ�����ƣ�
	public List<Song> showBy() {
		List<Song> songList = new ArrayList<Song>();
		Connection conn = DBUtil.getConnection();
		String sql = "SELECT * FROM tb_song ORDER BY hits DESC LIMIT 3";
		PreparedStatement ps = null;
		ResultSet rs = null;
		Song son=null;
		try {
			ps = conn.prepareStatement(sql);		
			rs = ps.executeQuery();// ��ѯ
			while (rs.next()) {
				son = new Song();
				son.setDownload(rs.getInt("download"));
				son.setFileSize(rs.getString("fileSize"));
				son.setFileURL(rs.getString("fileURL"));
				son.setFormat(rs.getString("format"));
				son.setHits(rs.getInt("hits"));
				son.setId(rs.getInt("id"));
				son.setSinger(rs.getString("singer"));
				son.setSongName(rs.getString("songName"));
				son.setSongType(rs.getString("songType"));
				son.setSpecialName(rs.getString("specialName"));
				son.setUpTime(rs.getDate("upTime"));
				songList.add(son);
			}
			
		} catch (Exception e) {
			Log.error("��ʾ��������ǰ��ĸ�����Ƴ��ִ���", e);
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}return songList;
	}

	@Override
	//10�� ��ʾר����special�������и���
	public Page<Song> showByspecial(String special,int pageno) {
		Page<Song> pageList = new Page<Song>();
		Connection conn = DBUtil.getConnection();
		String sql1 = "SELECT * FROM tb_song WHERE specialName = ?";
		String sql2 = "SELECT * FROM tb_song WHERE specialName = ? limit ?,?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		Song son=null;
		int sum = 0;
		try {
			ps = conn.prepareStatement(sql1);
			ps.setString(1, special);
			rs = ps.executeQuery();// ��ѯ
			if(rs.next()){
				sum = rs.getInt(1);
			}
			//�ȹر�����
			DBUtil.close(null, ps, rs);
			ps = conn.prepareStatement(sql2);
			ps.setString(1,special);// �������id		
			ps.setInt(2, (pageno-1)*10);
			ps.setInt(3, 10);
			rs = ps.executeQuery();// ��ѯ
			
			while (rs.next()) {
				son = new Song();
				son.setDownload(rs.getInt("download"));
				son.setFileSize(rs.getString("fileSize"));
				son.setFileURL(rs.getString("fileURL"));
				son.setFormat(rs.getString("format"));
				son.setHits(rs.getInt("hits"));
				son.setId(rs.getInt("id"));
				son.setSinger(rs.getString("singer"));
				son.setSongName(rs.getString("songName"));
				son.setSongType(rs.getString("songType"));
				son.setSpecialName(rs.getString("specialName"));
				son.setUpTime(rs.getDate("upTime"));
				pageList.getDatas().add(son);
			}
			pageList.setPageNums(sum%10+sum%10==0?0:1);
			
		} catch (Exception e) {
			Log.error("��ʾר����special�������и�����ִ���", e);
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}return pageList;
	}

	@Override
	//11�� ��ʾ�ø��֣�singer�������и���
	public Page<Song> showBysinger(String singer,int pageno) {
		Page<Song> pageList = new Page<Song>();
		Connection conn = DBUtil.getConnection();
		String sql = "SELECT * FROM tb_song WHERE singer = ?";
		String sql2 = "SELECT * FROM tb_song WHERE singer = ? limit ?,?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		Song son=null;
		int sum = 0;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, singer);
			rs = ps.executeQuery();// ��ѯ
			if(rs.next()){
				sum = rs.getInt(1);
			}
			//�ȹر�����
			DBUtil.close(null, ps, rs);
			ps = conn.prepareStatement(sql2);
			ps.setString(1,singer);// �������id		
			ps.setInt(2, (pageno-1)*10);
			ps.setInt(3, 10);
			rs = ps.executeQuery();// ��ѯ
			
			while (rs.next()) {
				son = new Song();
				son.setDownload(rs.getInt("download"));
				son.setFileSize(rs.getString("fileSize"));
				son.setFileURL(rs.getString("fileURL"));
				son.setFormat(rs.getString("format"));
				son.setHits(rs.getInt("hits"));
				son.setId(rs.getInt("id"));
				son.setSinger(rs.getString("singer"));
				son.setSongName(rs.getString("songName"));
				son.setSongType(rs.getString("songType"));
				son.setSpecialName(rs.getString("specialName"));
				son.setUpTime(rs.getDate("upTime"));
				pageList.getDatas().add(son);
			}
			pageList.setPageNums(sum%10+sum%10==0?0:1);
			
		} catch (Exception e) {
			Log.error("��ʾ�ø��֣�singer�������и�����ִ���", e);
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}return pageList;
	}
	//12�� ��ʾ�¸��ٵ�����ĳһ�׸�����飻
		public Page<Song> showByid(int id,int pageno) {
			Page<Song> pageList = new Page<Song>();
			Connection conn = DBUtil.getConnection();
			String sql = "SELECT * FROM tb_song WHERE id = ?";
			String sql2 = "SELECT * FROM tb_song WHERE id = ? limit ?,?";
			PreparedStatement ps = null;
			ResultSet rs = null;
			Song son=null;
			int sum = 0;
			try {
				ps = conn.prepareStatement(sql);
				ps.setInt(1, id);
				rs = ps.executeQuery();// ��ѯ
				if(rs.next()){
					sum = rs.getInt(1);
				}
				//�ȹر�����
				DBUtil.close(null, ps, rs);
				ps = conn.prepareStatement(sql2);
				ps.setInt(1, id);// �������id		
				ps.setInt(2, (pageno-1)*10);
				ps.setInt(3, 10);
				rs = ps.executeQuery();// ��ѯ
				
				while (rs.next()) {
					son = new Song();
					son.setDownload(rs.getInt("download"));
					son.setFileSize(rs.getString("fileSize"));
					son.setFileURL(rs.getString("fileURL"));
					son.setFormat(rs.getString("format"));
					son.setHits(rs.getInt("hits"));
					son.setId(rs.getInt("id"));
					son.setSinger(rs.getString("singer"));
					son.setSongName(rs.getString("songName"));
					son.setSongType(rs.getString("songType"));
					son.setSpecialName(rs.getString("specialName"));
					son.setUpTime(rs.getDate("upTime"));
					pageList.getDatas().add(son);
				}
				pageList.setPageNums(sum%10+sum%10==0?0:1);
				
			} catch (Exception e) {
				Log.error("��ʾ�ø��֣�singer�������и�����ִ���", e);
				e.printStackTrace();
			} finally {
				DBUtil.close(conn, ps, rs);
			}return pageList;
		}

		//13��ɾ�����
		@Override
		public int delete(int id) {
		Connection conn = DBUtil.getConnection();
		String sql = "delete From tb_song where id=?";
		PreparedStatement ps = null;
		int rs = 0;
		
		try {
			ps = conn.prepareStatement(sql);	
			ps.setInt(1, id);
			rs = ps.executeUpdate();// ��ѯ			
			
		} catch (Exception e) {
			Log.error("ɾ�������ִ���", e);
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, null);
		}return rs;
		}
		
		//14����Ӹ���
		public int addSong(Song song){
			Connection conn = DBUtil.getConnection();
			String sql = "insert into tb_song(songName,singer,specialName,fileSize,fileURL,format,upTime,songType) values (?,?,?,?,?,?,now(),?)";
			PreparedStatement ps = null;
			int count=0;	
			Song song1 ;
			try {
				ps = conn.prepareStatement(sql);			
				ps.setString(1, song.getSongName());
				ps.setString(2, song.getSinger());
				ps.setString(3, song.getSpecialName());
				ps.setString(4, song.getFileSize());
				
				ps.setString(5, song.getFileURL());
				ps.setString(6, song.getFormat());
				ps.setString(7, String.valueOf(song.getSongTypeId()));
				count=ps.executeUpdate();
				
			} catch (Exception e) {
				Log.error("���Ӹ�����ִ���", e);
				e.printStackTrace();
			} finally {
				DBUtil.close(conn, ps,null);
			}return count;
		}
		
		//��������
		public  List<Song>  continuePlay(String playId,String url,String condition){
			Connection conn = DBUtil.getConnection();
			List<Song> list = new ArrayList<Song>();
			String sql="SELECT * FROM tb_song WHERE id IN ("+playId+")"+condition;
			System.out.println("���SQL��䣺"+sql);
			PreparedStatement ps = null;
			ResultSet rs = null;
			Song songF=null;
			try {
				ps = conn.prepareStatement(sql);		
				rs = ps.executeQuery();// ��ѯ
				while (rs.next()) {
					songF = new Song();
					songF.setId(rs.getInt(1));
					songF.setSongName(rs.getString(2));
					songF.setSinger(rs.getString(3));
					songF.setSpecialName(rs.getString(4));
					songF.setFileSize(rs.getString(5));
					songF.setFileURL(url+rs.getString(6));
					songF.setFormat(rs.getString(7));
					songF.setHits(rs.getInt(8));
					songF.setDownload(rs.getInt(9));
					songF.setUpTime(rs.getDate(10));
					list.add(songF);						// ��ѡ��ĸ�����Ϣ���浽List������
				}
				
			} catch (Exception e) {
				Log.error("ʵ�ָ����������ִ���", e);
				e.printStackTrace();
			} finally {
				DBUtil.close(conn, ps,rs);
			}
			return list;
		}
		
		
		//��ȡ���������Ϣ
		public String[] tryListen(int id) {
			Connection conn = DBUtil.getConnection();
			String sql = "SELECT * FROM tb_song WHERE id="+id+"";
			PreparedStatement ps =null;
			ResultSet rs = null;
			String[] urlAndName=new String[2];
			String fileURL="";
			String songName="";
			try {
				ps = conn.prepareStatement(sql);		
				rs = ps.executeQuery();// ��ѯ
				if (rs.next()) {
					songName = rs.getString(2);
					fileURL=rs.getString(6);
					urlAndName[0]=songName;
					urlAndName[1]=fileURL;
				}
				
			} catch (Exception e) {
				Log.error("��ȡ���������ִ���", e);
				e.printStackTrace();
			} finally {
				DBUtil.close(conn, ps,rs);
			}
			return urlAndName;
		}
		
		// ����������
		public int holdoutAdd(int id) {
			
			String sql = "UPDATE tb_song SET hits=hits+1 WHERE id=" + id + "";
			Connection conn = DBUtil.getConnection();
			PreparedStatement ps = null;
			int rs = 0;
			try {
				ps = conn.prepareStatement(sql);		
				rs = ps.executeUpdate();// ��ѯ
			
				
			}catch (Exception e) {
				Log.error("�������������ִ���", e);
				e.printStackTrace();
			} finally {
				DBUtil.close(conn, ps,null);
			}
			return rs;
		}
		// ������ش���
		public int downloadAdd(int id) {
			String sql1 = "UPDATE tb_song SET download=download+1 WHERE id=" + id + "";
			Connection conn = DBUtil.getConnection();
			PreparedStatement ps = null;
			int rs = 0;
			try {
				ps = conn.prepareStatement(sql1);		
				rs = ps.executeUpdate();// ��ѯ
			
				
			}catch (Exception e) {
				Log.error("�������ش�����ִ���", e);
				e.printStackTrace();
			} finally {
				DBUtil.close(conn, ps,null);
			}
			return rs;
		}
		
		//��ø�������
		public String getFileURL(int id){
			String sql = "SELECT * FROM tb_song WHERE id="+id+"";
			Connection conn = DBUtil.getConnection();
			PreparedStatement ps = null;
			ResultSet rs = null;
			String fileURL="";
			try {
				ps=conn.prepareStatement(sql);		
				rs = ps.executeQuery();// ��ѯ
				if (rs.next()) {
					fileURL=rs.getString(6);
					
				}
				
			} catch (Exception e) {
				Log.error("��ȡ�������ӳ��ִ���", e);
				e.printStackTrace();
			} finally {
				DBUtil.close(conn, ps,null);
			}
			return fileURL;
		}
		//�û����ĸ���
		
		public List<Song> listened(int uid) {
			List<Song> songList = new ArrayList<Song>();
			Connection conn = DBUtil.getConnection();
			String sql = "SELECT * from tb_song where id IN(select songid from hitlist where userid = '"+uid+"')";
			PreparedStatement ps = null;
			ResultSet rs = null;
			Song son=null;
			try {
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();// ��ѯ
				while (rs.next()) {
					son = new Song();
					son.setDownload(rs.getInt("download"));
					son.setFileSize(rs.getString("fileSize"));
					son.setFileURL(rs.getString("fileURL"));
					son.setFormat(rs.getString("format"));
					son.setHits(rs.getInt("hits"));
					son.setId(rs.getInt("id"));
					son.setSinger(rs.getString("singer"));
					son.setSongName(rs.getString("songName"));
					son.setSongType(rs.getString("songType"));
					son.setSpecialName(rs.getString("specialName"));
					son.setUpTime(rs.getDate("upTime"));
					songList.add(son);
				}
				
			} catch (Exception e) {
				Log.error("�û����ĸ�����ִ���", e);
				e.printStackTrace();
			} finally {
				DBUtil.close(conn, ps, rs);
			}return songList;
		}

		//�û����ع�ĸ���
		
		public List<Song> downloaded(int uid) {
			List<Song> songList = new ArrayList<Song>();
			Connection conn = DBUtil.getConnection();
			String sql = "SELECT * from tb_song where id IN(select songid from downlist where userid = '"+uid+"')";
			PreparedStatement ps = null;
			ResultSet rs = null;
			Song son=null;
			try {
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();// ��ѯ
				while (rs.next()) {
					son = new Song();
					son.setDownload(rs.getInt("download"));
					son.setFileSize(rs.getString("fileSize"));
					son.setFileURL(rs.getString("fileURL"));
					son.setFormat(rs.getString("format"));
					son.setHits(rs.getInt("hits"));
					son.setId(rs.getInt("id"));
					son.setSinger(rs.getString("singer"));
					son.setSongName(rs.getString("songName"));
					son.setSongType(rs.getString("songType"));
					son.setSpecialName(rs.getString("specialName"));
					son.setUpTime(rs.getDate("upTime"));
					songList.add(son);
				}
				
			} catch (Exception e) {
				Log.error("�û����ع�ĸ�����ִ���", e);
				e.printStackTrace();
			} finally {
				DBUtil.close(conn, ps, rs);
			}return songList;
		}
		
		
		
		
		

}
